# -*- coding:UTF-8 -*-#

import traceback
from popcan.core.structures import Singleton
from popcan.helper.loggerhelper import QueueLogger,Module
from popcan.core.exceptions import DBAccessError

class BaseDao(Singleton):

    def __init__(self,pooldb = None):
        self.pooldb = pooldb

    def insert(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = cur.execute(sql,args)
            num,rowid = int(num),int(cur.lastrowid)
            if num <= 0:
                raise Exception("insert failure with num equals zero")
            conn.commit()
            return rowid
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'insert %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('insert','insert failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def insertmany(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num=cur.executemany(sql,args)
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'insertmany %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('insertmany','insertmany failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()            

    def insertbatch(self,inserters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            rowids = []
            for inserter in inserters:
                num = cur.execute(inserter[0],inserter[1])
                if num <= 0:
                    raise Exception("insert failure with num equals zero")
                rowids.append(int(cur.lastrowid))
            conn.commit()
            return rowids
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'insertbatch %s' % str(inserters), traceback.format_exc())
            raise DBAccessError('insertbatch','insertbatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def insertmanybatch(self,manyinserters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = 0
            for inserter in manyinserters:
                num += cur.execute(inserter[0],inserter[1])
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'insertmanybatch %s' % str(manyinserters), traceback.format_exc())
            raise DBAccessError('insertmanybatch','insertmanybatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def update(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = cur.execute(sql,args)
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'update %s %s' % (sql,args), traceback.format_exc())
            raise DBAccessError('update','update failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def updatemany(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num=cur.executemany(sql,args)
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'updatemany %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('updatemany','updatemany failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def updatebatch(self,updaters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = 0
            for updater in updaters:
                num +=cur.execute(updater[0],updater[1])
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'updatebatch %s' % str(updaters), traceback.format_exc())
            raise DBAccessError('updatebatch','updatebatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def updatemanybatch(self,manyupdaters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = 0
            for updater in manyupdaters:
                num += cur.executemany(updater[0],updater[1])
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'updatemanybatch %s' % str(manyupdaters), traceback.format_exc())
            raise DBAccessError('updatemanybatch','updatemanybatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def delete(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = cur.execute(sql,args)
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'delete %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('delete','delete failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def deletemany(self,sql,args):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = cur.executemany(sql,args)
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'deletemany %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('deletemany','deletemany failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def deletebatch(self,deleters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = 0
            for deleter in deleters:
                num += cur.execute(deleter[0,deleter[1]])
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'deletebatch %s' % str(deleters), traceback.format_exc())
            raise DBAccessError('deletebatch','deletebatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def deletemanybatch(self,manydeleters):
        conn,cur = None,None
        try:
            conn = self.pooldb.dedicated_connection()
            cur = conn.cursor()
            num = 0
            for deleter in manydeleters:
                num += cur.execute(deleter[0,deleter[1]])
            conn.commit()
            return num
        except:
            conn.rollback()
            QueueLogger().error(Module.DB, 'deletemanybatch %s' % str(manydeleters), traceback.format_exc())
            raise DBAccessError('deletemanybatch','deletemanybatch failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()

    def querylist(self,sql,args):
        conn,cur,result = None,None,()
        try:
            conn = self.pooldb.connection()
            cur = conn.cursor()
            num = cur.execute(sql,args)
            if num > 0:
                result = cur.fetchall()
            return result
        except:
            QueueLogger().error(Module.DB, 'querylist %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('querylist','querylist failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()
                
    def querymany(self,sql,args):
        conn,cur,result = None,None,()
        try:
            conn = self.pooldb.connection()
            cur = conn.cursor()
            for arg in args:
                num = cur.execute(sql,arg)
                if num > 0:
                    result += cur.fetchall()
            return result
        except:
            QueueLogger().error(Module.DB, 'querymany %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('querymany','querymany failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close() 

    def queryunique(self,sql,args):
        conn,cur,result = None,None,None
        try:
            conn = self.pooldb.connection()
            cur = conn.cursor()
            num = cur.execute(sql,args)
            if num > 0:
                result = cur.fetchone()
            return result
        except:
            QueueLogger().error(Module.DB, 'queryunique %s %s' % (sql,str(args)), traceback.format_exc())
            raise DBAccessError('queryunique','queryunique failure')
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()
    